package com.salary.service;

import com.salary.utils.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 读取Excel
 * 
 * @author lp
 * 
 */
public class ExcelRead {
	public int totalRows; // sheet中总行数
	public int totalCells; // 每一行总单元格数
	public List<ArrayList<String>> list;
	public List<ArrayList<String>> elist;
	/**
	 * read the Excel .xlsx,.xls
	 *jsp中的上传文件
	 * @return
	 * @throws IOException
	 */
	public ExcelRead() {
		elist = new ArrayList<ArrayList<String>>(); //存放一个excel筛选列后的数据
		// TODO Auto-generated constructor stub
	}

	public List<ArrayList<String>> readExcel(File file, String[] colarray, int xvhao) throws IOException {
		if (file == null || ExcelUtil.EMPTY.equals(file.getName().trim())) {
			return null;
		} else {
			String postfix = ExcelUtil.getPostfix(file.getName());
			if (!ExcelUtil.EMPTY.equals(postfix)) {
				if (ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
					return readXls(file, colarray, xvhao);
				} else if (ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
					return readXlsx(file, colarray, xvhao);
				} else {
					return null;
				}
			}
		}
		return null;
	}

	/**
	 * read the Excel 2010 .xlsx
	 * 
	 * @param file
	 * @return
	 * @throws IOException
	 */
	@SuppressWarnings("deprecation")
	public List<ArrayList<String>> readXlsx(File file, String[] colarray, int xvhao) {
		// IO流读取文件
		InputStream input = null;
		XSSFWorkbook wb = null;
		ArrayList<String> rowList = null;
		try {
			input = new FileInputStream(file);
			// 创建文档
			wb = new XSSFWorkbook(input);
			// 读取sheet(页)
			for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
				list = new ArrayList<ArrayList<String>>();  //存放一个excel文件所有数据
				XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
				totalRows = xssfSheet.getLastRowNum();
				if (totalRows == 0) {
					// continue;
					return null;
				}
				// 读取Row,从第一行开始
				for (int rowNum = 0; rowNum <= totalRows; rowNum++) {
					XSSFRow xssfRow = xssfSheet.getRow(rowNum);
					if (xssfRow != null) {
						rowList = new ArrayList<String>();
						totalCells = xssfRow.getLastCellNum();
						// 读取列，从第一列开始
						for (int c = 0; c < totalCells; c++) {
							XSSFCell cell = xssfRow.getCell(c);
							if (cell == null) {
								rowList.add(ExcelUtil.EMPTY);
								continue;
							}
							rowList.add(ExcelUtil.getXValue(cell).trim());
						}
						list.add(rowList);
					}
				}
				doColFilter(colarray, xvhao);
			}
			input.close();
			return list;
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				input.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return null;

	}

	/**
	 * read the Excel 2003-2007 .xls
	 * 
	 * @param file
	 * @param
	 * @param
	 * @return
	 * @throws IOException
	 */
	public List<ArrayList<String>> readXls(File file, String[] colarray, int xvhao) {
		// IO流读取文件
		InputStream input = null;
		HSSFWorkbook wb = null;
		ArrayList<String> rowList = null;
		try {
			input = new FileInputStream(file);
			// 创建文档
			wb = new HSSFWorkbook(input);
			// 读取sheet(页)
			for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
				list = new ArrayList<ArrayList<String>>();  //存放一个excel文件所有数据
				HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
				if (hssfSheet == null) {
					// continue;
					return null;
				}
				totalRows = hssfSheet.getLastRowNum();
				// 读取Row,从第一行开始
				for (int rowNum = 0; rowNum <= totalRows; rowNum++) {
					HSSFRow hssfRow = hssfSheet.getRow(rowNum);
					if (hssfRow != null) {
						rowList = new ArrayList<String>();
						totalCells = hssfRow.getLastCellNum();
						// 读取列，从第一列开始
						for (short c = 0; c < totalCells; c++) {
							HSSFCell cell = hssfRow.getCell(c);
							if (cell == null) {
								rowList.add(ExcelUtil.EMPTY);
								continue;
							}
							rowList.add(ExcelUtil.getHValue(cell).trim());
						}
						list.add(rowList);
					}
				}
				doColFilter(colarray, xvhao);
			}
			input.close();
			return list;
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				input.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public List<ArrayList<String>> doColFilter(String[] colarray, int xvhao) {
		
		ColFilter cf = new ColFilter();
		if(list.isEmpty()) {
			return null;
		}
		List<Integer> field = cf.doColFilter(list, colarray);
		ArrayList<String> record = new ArrayList<String>();
		for (int x = 0; x < list.size(); x++) {
			record = new ArrayList<String>();
			for (int y = 0; y < field.size(); y++) {
				int in = field.get(y);
				if (x == 0) {
					record.add(y, colarray[y]);
				} else if (in > -1) {
//					int i1 = list.get(x).size();
					List<String> ls = list.get(x);
					String st = new String();
					if(in < ls.size()){
						st = ls.get(in);
					} else {
						st = "";
					}
					record.add(y, st);
				} else {
					record.add(y, "0");
				}
				if(y == field.size()-1){
					String st = xvhao+"";
					record.add(y+1, st);
				}
			}
			elist.add(record);
		}
		return elist;
	}
	
}